package com.caocaog.market.data.transfer.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author liguang
 * @version 1.0.0
 * @Project jeecg-boot
 * @Package com.caocaog.market.data.transfer.mapper.xml
 * @ClassName CcAgentMapper.java
 * @createTime 2021年11月16日 09:40:00
 * @Description TODO
 */
public interface MkTestMapper {
    /**
     * 根据username查询系统中有没有该用户
     */
    @Select("select count(1) from sys_user where username=#{username}")
    int findForUsername(@Param("username") String username);
    /**
     * 插入商家账号信息
     */
    @Insert("INSERT INTO sys_user (id,username, realname, phone,PASSWORD,salt,STATUS,del_flag,activiti_sync,create_time,user_identity,user_type) " +
            "VALUES (#{id},#{username}, #{realname}, #{phone},'b32e957fd70fd535c370f6792f8fb583','rj8C06wL',1,0,0,#{nowDate},1,3)")
    int insertSysUser(@Param("id")String id ,
                      @Param("username")String username ,
                      @Param("realname")String realname ,
                      @Param("phone")String phone ,
                      @Param("nowDate")Date nowDate
                      );
    /**
     * 插入商家信息
     */
    @Insert("insert into mk_merchant_basic(id,name,idcard,front_idcard_url,back_idcard_url,hand_held_idcard_url,mobile," +
            "type,grade,state,alipay,aliname,site_id,user_id,lock_version,create_by,create_time,del_flag) values(" +
            "#{id},#{name},#{idcard},#{frontIdcardUrl},#{backIdcardUrl},#{handHeldIdcardUrl},#{mobile}," +
            "1,5,#{state},#{alipay},#{aliname},#{siteId},#{userId},1,'admin',#{createTime},0) ")
   int insertMerchant(@Param("id") String id,
                      @Param("name") String name,
                      @Param("idcard") String idcard,
                      @Param("frontIdcardUrl") String frontIdcardUrl,
                      @Param("backIdcardUrl") String backIdcardUrl,
                      @Param("handHeldIdcardUrl") String handHeldIdcardUrl,
                      @Param("mobile") String mobile,
                      @Param("state") int state,
                      @Param("alipay") String alipay,
                      @Param("aliname") String aliname,
                      @Param("siteId") String siteId,
                      @Param("userId") String userId,
                      @Param("createTime") Date createTime
                      );
    @Select("select id from mk_site_basic where pt_agent_id=#{ptAgentId}")
    String findSiteId(@Param("ptAgentId") int ptAgentId);

    @Select("select id from sys_user where username=#{loginName} and user_type=3")
    String findUserId(@Param("loginName") String loginName);

    @Select("select count(1) from mk_merchant_basic where id=#{id}")
    int findMerchantCountByUserId(@Param("id") String id);

    @Select("select count(1) from mk_bind_shop_member where shop_id= #{shopId}")
    int findBinded(@Param("shopId")String shopId);

    @Insert("insert into mk_bind_shop_member(id,create_by,create_time,lock_version,del_flag,merchant_id,shop_id,pt_id,pt_login_name) " +
            "values (#{id},'admin',#{createTime},1,0,#{merchantId},#{shopId},#{ptId},#{ptLoginName})")
    int insertBindShopMember(@Param("id")String id,
                             @Param("createTime")Date createTime,
                             @Param("merchantId")String merchantId,
                             @Param("shopId")String shopId,
                             @Param("ptId")String ptId,
                             @Param("ptLoginName")String ptLoginName
                             );
    /**
     * 先查询该门店是否已经插入到mk_shop_basic表中
     */
    @Select("select count(1) from mk_shop_basic  where id= #{shopId}")
    int findShopIsExists(@Param("shopId") String shopId);

    /**
     * 插入门店数据
     */
    @Insert("INSERT INTO mk_shop_basic(id,create_by,create_time,lock_version,del_flag,merchant_id,NAME,logo,STATUS,business_std,business_end,is_rest,license,commission_rate,LEVEL,is_hot,recommend," +
            "lng,lat,province,city,AREA,location,address,address_text,channel_id,TYPE,zfb_name,zfb_account,audit,grade) " +
            "VALUES(#{id},'admin',#{nowDate},1,0,#{merchantId},#{name},#{logo},0,#{businessStd},#{businessEnd},#{isRest},#{license}," +
            "#{commossionRate},5,0,#{isRecommend},#{lng},#{lat},#{province},#{city},#{area},#{location},#{address}," +
            "#{addressText},#{channelId},1,#{zfbName},#{zfbNo},#{audit},#{score})")
    int insShop(
            @Param("id") String id,
            @Param("nowDate") Date nowDate,
            @Param("merchantId") String merchantId,
            @Param("name") String name,
            @Param("logo") String logo,
            @Param("businessStd") String businessStd,
            @Param("businessEnd") String businessEnd,
            @Param("isRest") int isRest,
            @Param("license") String license,
            @Param("commossionRate") String commossionRate,
            @Param("isRecommend") String isRecommend,
            @Param("lng") String lng,
            @Param("lat") String lat,
            @Param("province") String province,
            @Param("city") String city,
            @Param("area") String area,
            @Param("location") String location,
            @Param("address") String address,
            @Param("addressText") String addressText,
            @Param("channelId") String channelId,
            @Param("zfbName") String zfbName,
            @Param("zfbNo") String zfbNo,
            @Param("audit") int audit,
            @Param("score") String score);


    /**
     * 查询频道id
     */
    @Select("select id from mk_shop_channel where parent_id = (select id from mk_shop_channel where trim(name)=#{name}) and trim(name)='全部'")
    String findChannelId(@Param("name") String name);

    @Insert(" INSERT INTO `mk_shop_takeout` (id,create_by,create_time,lock_version,del_flag,shop_id,text_total,duration,per_cost," +
            " start_fare,free_distribution,mx_distance,free_distance,pack,oder_model,channel_id,appo_model) " +
            " values(#{id},'admin',#{nowDate},1,0,#{shopId},#{textTotal},#{duration},#{perCost},#{startFare}," +
            "#{freeDistribution},#{mxDistance},#{freeDistance},#{pack},#{orderModel},#{channelId},'1')")
    int insertShopTakeout(
            @Param("id") String id,
            @Param("nowDate") Date nowDate,
            @Param("shopId") String shopId,
            @Param("textTotal") int textTotal,
            @Param("duration") String duration,
            @Param("perCost") String perCost,
            @Param("startFare") String startFare,
            @Param("freeDistribution") String freeDistribution,
            @Param("mxDistance") String mxDistance,
            @Param("freeDistance") String freeDistance,
            @Param("pack") String pack,
            @Param("orderModel") String orderModel,
            @Param("channelId") String channelId );

    //@Select("select id from mk_shop_basic where create_by='admin' ")
    // 未审核的门店信息
    @Select("select id from mk_shop_basic where create_by='admin' and create_time='2022-01-11 11:56:24' and audit=0")
    List<Integer> findAllShopIds();


   /**
    * 插入商品分类数据
    */
   @Insert(" insert into mk_goods_sort(id,create_by,create_time,lock_version,shop_id,name,mark,del_flag,sort,is_show) " +
           "values(#{id},'admin',#{nowDate},1,#{shopId},#{name},#{mark},0,#{sort},#{isShow}) ")
   int insertGoodsSort(@Param("id") String id,
                       @Param("nowDate") Date nowDate,
                       @Param("shopId") String shopId,
                       @Param("name") String name,
                       @Param("mark") String mark,
                       @Param("sort") int sort,
                       @Param("isShow") String isShow
                       );
   @Select("select count(1) from mk_goods_sort where id=#{id}")
    int findSortById(@Param("id") String id);

    /**
     * 存商品分类中间表
     */
    @Insert("insert into mk_goods_sort_info(id,create_by,create_time,sort_id,goods_id) values(#{id},'admin',#{nowDate},#{sortId},#{goodsId})")
    int insertSortInfo(@Param("id") String id,
                       @Param("nowDate") Date nowDate,
                       @Param("sortId") String sortId,
                       @Param("goodsId") String goodsId );
    /**
     * 存商品属性
     */
    @Insert("insert into mk_goods_attribute(id,create_by,create_time,lock_version,goods_id,name,value,del_flag) " +
            "values(#{id},'admin',#{nowDate},1,#{goodsId},#{name},#{value},0)")
    int insertGoodsAttribute(
            @Param("id") String id,
            @Param("nowDate") Date nowDate,
            @Param("goodsId") String goodsId,
            @Param("name") String name,
            @Param("value") String value );
    /**
     * 查询商品规格是否已转移过来
     */
    @Select("select count(1) from mk_goods_specifications where id=#{id}")
    int findSpecificationsById(@Param("id")String id);

    /**
     * 存商品规格
     */
    @Insert("insert into mk_goods_specifications(id,create_by,create_time,lock_version,goods_id,name,description,price,weight,stock,pack_fee,picture,del_flag) " +
            "values(#{id},'admin',#{nowDate},1,#{goodsId},#{name},#{description},#{price},#{weight},#{stock},#{packFee},#{picture},0)")
    int insertSpecifications(
            @Param("id") String id,
            @Param("nowDate") Date nowDate,
            @Param("goodsId") String goodsId,
            @Param("name") String name,
            @Param("description") String description,
            @Param("price") String price,
            @Param("weight") String weight,
            @Param("stock") int stock,
            @Param("packFee") String packFee,
            @Param("picture") String picture
    );
    /**
     * 查询商品是否存在
     */
    @Select("select count(1) from mk_goods_basic where id=#{id}")
    int findGoodsIsExists(@Param("id") String id);
    /**
     * 存商品数据
     */
    @Insert("insert into mk_goods_basic (id,create_by,create_time,lock_version,del_flag,name,short_name,sorts,original_price,present_price,code,picture,description,short_description,status,is_index,is_hot," +
            "visit_num,sale_num,shop_id,sales_quantity) values(#{id},'admin',#{nowDate},1,0,#{name},#{shortName},#{sorts},#{originalPrice},#{presentPrice}," +
            "#{code},#{picture},#{description},#{shortDescription},#{status},#{isIndex},#{isHot},#{visitNum},#{saleNum},#{shopId},1)")
    int insertGoodsBasic(
            @Param("id") String id,
            @Param("nowDate") Date nowDate,
            @Param("name") String name,
            @Param("shortName") String shortName,
            @Param("sorts") String sorts,
            @Param("originalPrice") String originalPrice,
            @Param("presentPrice") String presentPrice,
            @Param("code") String code,
            @Param("picture") String picture,
            @Param("description") String description,
            @Param("shortDescription") String shortDescription,
            @Param("status") String status,
            @Param("isIndex") String isIndex,
            @Param("isHot") String isHot,
            @Param("visitNum") int visitNum,
            @Param("saleNum") int saleNum,
            @Param("shopId") String shopId );

    /**
     * 查询所有商品数据
     */
    @Select(" select id,goods_id as goodsId from mk_goods_his  where create_by='admin' AND create_time='2022-01-09 17:12:14' ")
    List<Map<String, String>> findMkGoodsHis();

    /**
     * 根据商品id查询该商品的所有属性对象
     */
    @Select(" select id,create_by as createBy,create_time as createTime,del_flag as delFlag,goods_id as goodsId,lock_version as lockVersion," +
            " name,sys_org_code as sysOrgCode,value from mk_goods_attribute where goods_id=#{goodsId}")
    List<Map<String,Object>> findGoodsAttrByGoodsId(@Param("goodsId") String goodsId);

    /**
     * 根据商品id查询商品的所有规格对象
     */
    @Select("select id,create_by as createBy,create_time as createTime,del_flag as delFlag,goods_id as goodsId,lock_version as lockVersion," +
            " name,description,price,weight,stock,pack_fee as packFee,picture from mk_goods_specifications where goods_id=#{goodsId}")
    List<Map<String,Object>> findGoodsSpecByGoodsId(@Param("goodsId") String goodsId);
   /**
    * 更新历史表（mk_goods_his）中的
    *     goods_attribute（商品属性）字段
    *     goods_specifications（商品规格）字段
    */
   @Update("update mk_goods_his set goods_attribute=#{goodsAttribute},goods_specifications= #{goodsSpecs} where id=#{id}")
   int updateGoodsHisAttrAndSpec(@Param("goodsAttribute") String goodsAttribute,@Param("goodsSpecs") String goodsSpecs,@Param("id") String id);

    /**
     * 根据曹操跑腿库中的代理商id查询曹操来了库中的分站id
     */
    @Select("select id from mk_site_basic where pt_agent_id=#{agentSid}")
    String findMkSiteIdByAgentSid(@Param("agentSid") int agentSid);

    /**
     * 根据shopId 查询  该门店所对应的商家id
     */
    @Select("select merchant_id from mk_shop_basic where id=#{shopId}")
    String findMerchantIdByShopId(@Param("shopId") String shopId);
   /**
    * 插入订单数据
    */
   @Insert("insert into mk_order_base(id,create_by,create_time,lock_version,del_flag,site_id,merchant_id,shop_id,order_no," +
           "type,status,source,is_show,remark,member_id) values(#{id},'admin',#{nowDate},1,0,#{siteId},#{merchantId},#{shopId},#{orderNo}," +
           "#{type},#{status},#{source},0,#{remark},#{memberId})")
   int insertMkOrder(@Param("id") String id,
                     @Param("nowDate") Date nowDate,
                     @Param("siteId") String siteId,
                     @Param("merchantId") String merchantId,
                     @Param("shopId") String shopId,
                     @Param("orderNo") String orderNo,
                     @Param("type") int type,
                     @Param("status") String status,
                     @Param("source") int source,
                     @Param("remark") String remark,
                     @Param("memberId") String memberId
                     );
    /**
     * 存订单的配送信息
     */
    @Insert("insert into mk_order_delivery(id,create_by,create_time,lock_version,del_flag,order_id,name,mobile," +
            "location,address,lng,lat,distance,delivery_type,delivery_amount ) values(#{id},'admin',#{nowDate},1,0," +
            "#{orderId},#{name},#{mobile},#{location},#{address},#{lng},#{lat},#{distance},1,#{deliveryAmount})")
    int insertMkOrderDelivery(@Param("id") String id,
                              @Param("nowDate") Date nowDate,
                              @Param("orderId") String orderId,
                              @Param("name") String name,
                              @Param("mobile") String mobile,
                              @Param("location") String location,
                              @Param("address") String address,
                              @Param("lng") String lng,
                              @Param("lat") String lat,@Param("distance") String distance,@Param("deliveryAmount") String deliveryAmount
                              );
    /**
     * 存订单行信息
     */
    @Insert("insert into mk_order_line(id,create_by,create_time,lock_version,del_flag,order_id,order_no,goods_his_id,spec_his_id," +
            "quantity,total_price,remark,name_str,goods_name) values(#{id},'admin',#{nowDate},1,0,#{orderId}," +
            "#{orderNo},#{goodsHisId},#{specHisId},#{quantity},#{totalPrice},#{remark},#{nameStr},#{goodsName})")
    int insertMkOrderLine(
            @Param("id") String id,
            @Param("nowDate") Date nowDate ,
            @Param("orderId") String orderId ,
            @Param("orderNo") String orderNo ,
            @Param("goodsHisId") String goodsHisId ,
            @Param("specHisId") String specHisId ,
            @Param("quantity") int quantity ,
            @Param("totalPrice") String totalPrice ,
            @Param("remark") String remark ,
            @Param("nameStr") String nameStr ,
            @Param("goodsName") String goodsName );

    /**
     * 存支付信息
     */
    @Insert("insert into mk_order_pay(id,create_by,create_time,lock_version,del_flag,order_id,pay_status," +
            "pay_way,pay_serno,pay_time,amount,discount_amount,delivery_amount,pack_amount,pay_amount,end_pay_info) " +
            "values(#{id},'admin',#{nowDate},1,0,#{orderId},#{payStatus},#{payWay},#{paySerno},#{payTime},#{amount}," +
            "#{discountAmount},#{deliveryAmount},#{packAmount},#{payAmount},#{endPayInfo})")
    int insertMkOrderPay(
            @Param("id") String id,
            @Param("nowDate") Date nowDate,
            @Param("orderId") String orderId,
            @Param("payStatus") int payStatus,
            @Param("payWay") int payWay,
            @Param("paySerno") String paySerno,
            @Param("payTime") Date payTime,
            @Param("amount") String amount,
            @Param("discountAmount") String discountAmount,
            @Param("deliveryAmount") String deliveryAmount,
            @Param("packAmount") String packAmount,
            @Param("payAmount") String payAmount,
            @Param("endPayInfo") String endPayInfo );

    /**
     * 查询转移过来的商家用户
     */
    @Select("select id,username from sys_user where user_type=3 AND salt='rj8C06wL'")
    List<Map<String,String>> findUserList();

    @Update("update sys_user set password=#{password},salt=#{salt} where id=#{id}")
    int updatePass(@Param("password") String password,@Param("salt") String salt,@Param("id") String id);

    /**
     * 需要更改商品分类的所有商品（主要针对从原系统中拉取过来的数据）
     */
    @Select("select id,sorts from mk_goods_basic where create_by='admin'  AND (create_time='2022-01-11 14:31:07' OR create_time='2022-01-09 17:12:14') AND sorts NOT LIKE '%,%'  AND LENGTH(sorts)>10 ")
    List<Map<String, String>> findGoodsForUpdateSorts();
    /**
     * 查询商品id查询所有的商品分类
     */
    @Select("select id,sort_id as sortId from mk_goods_sort_info where goods_id=#{goodsId} ")
    List<Map<String, String>> findSortByGoodsId(@Param("goodsId") String goodsId);
    /**
     * 修改商品表中的sorts字段
     */
    @Update("update mk_goods_basic set sorts=#{sorts} where id=#{goodsId}")
    int updateSortsByGoodsId(@Param("sorts") String sorts,@Param("goodsId") String goodsId);

    /**
     * 查询表中没有省、市、县  的门店数据
     */
    @Select("select id,concat(lng,',',lat) as lngLat  from mk_shop_basic where ifnull(province,'')='' and ifnull(city,'')='' and ifnull(area,'')='' ")
    List<Map<String,String>> findShopForArea();

    /**
     * 根据adcode查询 省、市、县/区的id
     */
    @Select("select sid as areaId, p_sid as provinceId,s_sid cityId from mk_area_district where adcode=#{adcode} limit 1")
    Map<String, String> findArea(@Param("adcode") String adcode);
    /**
     * 根据adcode查询 省、市 的id
     */
    @Select("select sid as cityId, p_sid as provinceId from mk_area_city where adcode=#{adcode} limit 1")
    Map<String, String> findCity(@Param("adcode") String adcode);

    @Update("update mk_shop_basic set province=#{province},city=#{city},area=#{area} where id=#{id}")
    int updShopArea(@Param("province") String province,@Param("city") String city,
                    @Param("area") String area,@Param("id") String id);
}
